<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">
<html><head><meta name="qrichtext" content="1" /><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><style type="text/css">
p, li { white-space: pre-wrap; }
</style></head><body style=" font-family:'MS Shell Dlg 2'; font-size:11pt; font-weight:400; font-style:normal;">
<p style=" margin-top:0px; margin-bottom:4px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:4px;"><span style=" font-family:'DejaVu Sans';">Подключение из Excel к Access через VBA</span></p>
<p style=" margin-top:0px; margin-bottom:4px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:4px;"><span style=" font-family:'DejaVu Sans';">Полезная функция по подключению из Excel к Access (предварительно надо подключить библиотеку MS ActiveX Data Objects 2.8 Library, как показано на картинке). Проверено - работает.</span></p>
<p style=" margin-top:0px; margin-bottom:4px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:4px;"><span style=" font-family:'DejaVu Sans';">VBA Добавление Reference</span></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'DejaVu Sans';"><br /></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">Public Sub test_db()</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">     </span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    ConnectionString = &quot;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=&quot; &amp; ActiveWorkbook.Path &amp; &quot;\Database4.accdb; Jet OLEDB:Database;&quot;</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">     </span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    Dim con As New ADODB.Connection</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    con.Open ConnectionString</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">     </span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    On Error GoTo not_table</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    con.Execute (&quot;SELECT TOP 1 * FROM Customers&quot;)</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    con.Close</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    Exit Sub</span></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'Consolas'; font-size:9pt; color:#00007f;"><br /></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">not_table:</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">    con.Close</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">     </span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f;">End Sub</span></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'Consolas'; font-size:9pt; color:#00007f;"><br /></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><a href="https://coderoad.ru/30364090/%D0%A1%D1%87%D0%B5%D1%82%D1%87%D0%B8%D0%BA-%D0%BD%D0%B0%D0%B1%D0%BE%D1%80%D0%BE%D0%B2-%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D0%B5%D0%B9-%D0%B2%D1%81%D0%B5%D0%B3%D0%B4%D0%B0-%D0%B2%D0%BE%D0%B7%D0%B2%D1%80%D0%B0%D1%89%D0%B0%D0%B5%D1%82-%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B5-1"><span style=" font-family:'Consolas'; font-size:9pt; text-decoration: underline; color:#0000ff;">https://coderoad.ru/30364090/%D0%A1%D1%87%D0%B5%D1%82%D1%87%D0%B8%D0%BA-%D0%BD%D0%B0%D0%B1%D0%BE%D1%80%D0%BE%D0%B2-%D0%B7%D0%B0%D0%BF%D0%B8%D1%81%D0%B5%D0%B9-%D0%B2%D1%81%D0%B5%D0%B3%D0%B4%D0%B0-%D0%B2%D0%BE%D0%B7%D0%B2%D1%80%D0%B0%D1%89%D0%B0%D0%B5%D1%82-%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B5-1</span></a></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; color:#212529; background-color:#ffffff;">Вам нужно использовать статический курсор. Для этого вам нужно явно создать объект RecordSet, вот так:</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#f6f8fa;"><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#660066; background-color:#000000;">Set</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> objRS </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">=</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#660066; background-color:#000000;">Server</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">.</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#660066; background-color:#000000;">CreateObject</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">(</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#008800; background-color:#000000;">&quot;ADODB.Recordset&quot;</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">)</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#f6f8fa;"><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;">objRS</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">.</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#660066; background-color:#000000;">CursorLocation</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">=</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> adUseClient</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#f6f8fa;"><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;">objRS</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">.</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#660066; background-color:#000000;">Open</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#008800; background-color:#000000;">&quot;SELECT * FROM &quot;</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">&amp;</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> viewName </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">&amp;</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> </span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#008800; background-color:#000000;">&quot;;&quot;</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">,</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> objConn</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">,</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> adOpenStatic</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">,</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;">adLockReadOnly</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#666600; background-color:#000000;">,</span><span style=" font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#000000;"> adCmdText</span></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'SFMono-Regular,Menlo,Monaco,Consolas,Liberation Mono,Courier New,monospace'; font-size:14px; color:#000000; background-color:#f6f8fa;"><br /></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f; background-color:#000000;">Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f; background-color:#000000;">objRS.CursorLocation = adUseClient</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:20px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-family:'Consolas'; font-size:9pt; color:#00007f; background-color:#000000;">objRS.Open &quot;SELECT * FROM &quot; &amp; viewName &amp; &quot;;&quot;, objConn, adOpenStatic,adLockReadOnly, adCmdText</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#ffffff;"><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; color:#212529; background-color:#ffffff;">Неявно созданные RecordSets имеют серверные курсоры, что приводит к возвращению значения -1.</span></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#ffffff;"><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; color:#212529; background-color:#ffffff;">Для получения дополнительной информации:</span><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; color:#212529;"> </span><a href="http://www.adopenstatic.com/faq/recordcounterror.asp"><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; text-decoration: underline; color:#1675d1; background-color:transparent;">http://www.adopenstatic.com/faq/recordcounterror.asp</span></a></p>
<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; color:#000000; background-color:#ffffff;"><br /></p>
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; background-color:#ffffff;"><a href="http://www.adopenstatic.com/faq/recordcounterror.asp"><span style=" font-family:'-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Helvetica Neue,Arial,Noto Sans,sans-serif,Apple Color Emoji,Segoe UI Emoji,Segoe UI Symbol,Noto Color Emoji'; font-size:16px; text-decoration: underline; color:#0000ff; background-color:transparent;">http://www.adopenstatic.com/faq/recordcounterror.asp</span></a></p>
<table border="0" style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="100%" style=" vertical-align:top;">
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">NOTE: </span><a href="http://www.adopenstatic.com/faq/recordcountalternatives.asp"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; text-decoration: underline; color:#333333; background-color:#ffffff;">click here</span></a><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;"> to see superior alternatives to .RecordCount.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; font-weight:600; color:#000000;">RecordCount returns -1</span><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;"><br />The use of the ADO Recordset's .RecordCount property requires either the use of:</span></p>
<ul style="margin-top: 0px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; -qt-list-indent: 1;"><li style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;" style=" margin-top:12px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-size:13px;">Static or Keyset server-side cursors or</span></li>
<li style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;" style=" margin-top:0px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-size:13px;">A client-side cursor (which returns a Static cursor)</span></li></ul>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">(Note: some OLEDB Providers will return the correct recordcount with an adOpenDynamic cursor, others will not).</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">By default Recordsets are opened server-side, and with an adOpenForwardOnly cursor. Attempting to access the .RecordCount property with this type of cursor will return -1.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">The easiest way to fix this is to change the cursor type to adOpenStatic. Doing this requires you to explicitly create a recordset object:</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; line-height:18px; background-color:transparent;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#990000; background-color:transparent;">Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)</span><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#990000;"><br />objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">Attempting to implicitly create a recordset, eg like this:</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; line-height:18px; background-color:transparent;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#990000; background-color:transparent;">Set objRS = objConn.execute(strSQL)</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">will not work, as the implicitly created recordset will have a default adOpenForwardOnly cursor.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">As mentioned above, the alternative method is to use a client-side cursor. The client referred in this case is the OLEDB Cursor Service.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:40px; margin-right:40px; -qt-block-indent:0; text-indent:0px; line-height:18px; background-color:transparent;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#990000; background-color:transparent;">Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)</span><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#990000;"><br />objRS.CursorLocation = adUseClient<br />objRS.Open strSQL, objConn,,adLockReadOnly, adCmdText<br /></span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">In order to be able to use server-side cursors and .Recordcount, the Recordset object must support either Approximate Positioning or Bookmarking. There has been discussion on the ActiveServerPages list to the effect that the MS Oracle OLEDB Provider (or earlier versions of this provider) do not support either Approximate Positioning or Bookmarking, hence require client-side cursors in order for .RecordCount to work.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">The use of ADO constants requires you to define them. You can get information on doing this </span><a href="http://www.adopenstatic.com/faq/800a0bb9step2.asp"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; text-decoration: underline; color:#333333; background-color:#ffffff;">here</span></a><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">. For more information on the Recordset's .Open method </span><a href="http://www.adopenstatic.com/faq/800a0bb9.asp#RecordsetOpenSyntax"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; text-decoration: underline; color:#333333; background-color:#ffffff;">click here</span></a><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; color:#000000;">.</span></p>
<p style=" margin-top:12px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; line-height:18px;"><a href="http://www.adopenstatic.com/faq/"><span style=" font-family:'Verdana,Geneva,Arial,sans-serif'; font-size:8.25pt; text-decoration: underline; color:#333333; background-color:#ffffff;">Back to FAQ Listing</span></a></p></td></tr></table></body></html>